package ws.jonas.hoarder.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public abstract class SqlDb {
	protected PreparedStatement selectProductIdPs;
	protected PreparedStatement selectStoreIdPs;
	protected PreparedStatement insertProductPs;
	protected PreparedStatement insertStorePs;
	protected PreparedStatement insertStockPs;
	protected Connection conn;
	
	/**
	 * @return true if any modification was done to the database. 
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addProductIfNotExists(int prodnr, String name, String volume) throws SQLException {
		selectProductIdPs.setInt(1, prodnr);
		selectProductIdPs.setString(2, volume);
		ResultSet selectRs = selectProductIdPs.executeQuery();
		if (selectRs.next()) {
			return false;
		} else {
			insertProduct(prodnr, name, volume);
			return true;
		}
	}
	
	/**
	 * @return true if any modification was done to the database.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStoreIfNotExists(String city, String address, String county) throws SQLException {
		selectStoreIdPs.setString(1, city);
		selectStoreIdPs.setString(2, address);
		selectStoreIdPs.setString(3, county);
		ResultSet selectRs = selectStoreIdPs.executeQuery();
		if (selectRs.next()) {
			return false;
		} else {
			insertStore(address, city, county);
			return true;
		}
	}
	
	/**
	 * @return true.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStock(int prodnr, String name, String volume, String county, String city, String address, int qty) throws SQLException {
		insertStock(prodnr, volume, address, city, county, qty);
		return true;
	}
	
	protected void insertProduct(int prodnr, String name, String volume) throws SQLException {
		insertProductPs.setInt(1, prodnr);
		insertProductPs.setString(2, volume);
		insertProductPs.setString(3, name);
		insertProductPs.executeUpdate();
	}

	protected void insertStore(String address, String city, String county) throws SQLException {
		insertStorePs.setString(1, city);
		insertStorePs.setString(2, address);
		insertStorePs.setString(3, county);
		insertStorePs.executeUpdate();
	}

	protected void insertStock(int prodnr, String volume, String address, String city, String county, int quantity) throws SQLException {
		insertStockPs.setInt(1, prodnr);
		insertStockPs.setString(2, volume);
		insertStockPs.setString(3, city);
		insertStockPs.setString(4, address);
		insertStockPs.setString(5, county);
		insertStockPs.setInt(6, quantity);
		insertStockPs.executeUpdate();
	}
}
